Drilling through Reports to Obtain Greater Detail

One of the unique features that Argos provides is the ability for the Report Viewer to click on an object in a report and drill down to display a greater level of detail for the object.  Consider the Dashboard in the figure below which uses sales information from the Sample Database.

• The bar chart at the top displays a summary of sales for each employee from 2005 – 2009.
• Clicking any of the bars (employees) will drill down and display daily sales details for the selected employee in the multi-column list box and in the line chart at the bottom.

The detail information is not displayed until one of the employees is selected in the bar chart.

For example, selecting employee Patterson displays the detail information in the following figure:

Using what you've learned so far, let's walk through creating this dashboard.

1. Create a bar chart at the top of the report area.  Name the chart 'Chart1' with a dataset series named Series_1. 
2. In the dataset's SQL query, put the employee's last name and the total sales into the SELECT clause.
3. Turn on Summing, group by the employee's last name, and sum the total sales.
4. In the WHERE clause, limit search results to sale dates between 2005 and 2009.
5. Finish designing the bar chart and return to the DataBlock Designer.
6. Place an edit box () below the bar chart. Make the edit box data aware and set the text to 'Chart1.Series_1.last_name', so that it displays the name of the employee selected on the bar chart.
7. Place a multi-column listbox in the middle of the report area.  Name the listbox MultiColumn2.
8. In the SELECT clause of the SQL query, enter the sale date (named Sale_Date) and the quantity (named Daily_Sales).
9. Turn on Summing, group by the sale date, and sum the quantity.
10. In the WHERE clause, limit search results to the last name of the employee selected in the bar chart.
11. Also in the WHERE clause, limit search results to sale dates between 2005 and 2009.
12. Copy the query, click OK, and validate the query to return to the DataBlock Designer.
13. Place a second chart below the multi-column listbox. Name the chart Chart2 with a dataset names Series_2.
14. Paste the query you copied from the multi-column listbox.
15. Set the chart style to LIne.
16. Save your changes and test the dashboard.

Tip: Don't forget to join your tables!

Summary

The procedure to allow drilling down within a report is a straightforward process.  The key is to include a common item (last_name in this example) in the WHERE clause of the query used to obtain the detail information, and link it to the variable representing the selected object.  

Save this as a Dashboard.